package com.yanyun.yczyy.api.repository.impl;

import com.yanyun.yczyy.api.entities.*;
import com.yanyun.yczyy.api.repository.IPlatformApiRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Slf4j
@Repository
public class PlatformApiRepository implements IPlatformApiRepository {

    @Override
    public HomeSecurityResponse homeSecurity() {
        final String sql = "select ifnull(count(1)    ,0)                                    total, " +
                "       ifnull(sum(case when value = '在线' then 1 else 0 end),0) online_size, " +
                "       ifnull(sum(case when value = '离线' then 1 else 0 end),0) offline_size " +
                "from tbl_io_data m, " +
                "     (select resource_id, max(io_time) io_time " +
                "      from tbl_io_data " +
                "      where io_time >= DATE_ADD(NOW(), INTERVAL -1 hour) " +
                "        and sys_name = '视频监控系统' " +
                "      group by resource_id) l " +
                "where m.resource_id = l.resource_id " +
                "  and m.io_time = l.io_time " +
                "order by m.resource_id";
        try {
            return yczyy.queryForObject(sql,_mapper_homeSecurity);
        }catch (Exception e){
            log.error("",e);
            return new HomeSecurityResponse();
        }
    }

    @Override
    public HomeParkResponse homePark() {
        final String sql = "select ifnull(count(1)       ,0)                                           total, " +
                "       ifnull(sum(case when port_status = 'occupied' then 1 else 0 end) ,0) occupied_size, " +
                "       ifnull(sum(case when port_status = 'empty' then 1 else 0 end) ,0)   empty_size " +
                "from tbl_park_guide";
        try {
            return yczyy.queryForObject(sql,_mapper_homePark);
        }catch (Exception e){
            log.error("",e);
            return new HomeParkResponse();
        }
    }

    @Override
    public HomeCtpResponse homeCtp() {
        final String sql = "SELECT ifnull(count(1),0)                                           total, " +
                "       ifnull(sum(case when DeviceOnline = 1 then '1' else 0 end),0) online_size, " +
                "       ifnull(sum(case when DeviceOnline = 0 then '1' else 0 end),0) offline_size " +
                "FROM `departmentdevice`";
        try {
            return ctp.queryForObject(sql,_mapper_homeCtp);
        }catch (Exception e){
            log.error("",e);
            return new HomeCtpResponse();
        }
    }

    @Override
    public List<HomeLocationSecurityResponse> homeLocationSecurity() {
        final String sql = "select case when locate('医研', resource_name) > 1 then '医研楼' else '门诊楼' end location, " +
                "       count(1)                                                                    total, " +
                "       sum(case when value = '在线' then 1 else 0 end)                             online_size, " +
                "       sum(case when value = '离线' then 1 else 0 end)                             offline_size " +
                "from tbl_io_data m, " +
                "     (select i1.resource_id, max(i1.io_time) io_time " +
                "      from tbl_io_data i1 " +
                "      where i1.io_time >= DATE_ADD(NOW(), INTERVAL -1 hour) and i1.io_time < CURRENT_TIMESTAMP() " +
                "        and i1.sys_name = '视频监控系统' " +
                "        and (locate('医研', resource_name) > 1 or locate('门诊', resource_name) > 1) " +
                "      group by i1.resource_id) l " +
                "where m.resource_id = l.resource_id " +
                "  and m.io_time = l.io_time " +
                "  and m.io_time >= DATE_ADD(NOW(), INTERVAL -1 hour) " +
                "  and m.io_time < CURRENT_TIMESTAMP() " +
                "group by case when locate('医研', resource_name) > 1 then '医研楼' else '门诊楼' end " +
                "order by m.resource_id";
        try {
            return yczyy.query(sql,_mapper_homeLocationSecurity);
        }catch (Exception e){
            log.error("",e);
            return null;
        }
    }

    @Override
    public List<HomeEnergy> homeEnergy() {
        final String sql = "select REPLACE(replace(replace(t.resource_name,'门诊楼地下一层_',''),'DP1进线柜',''),'正向有功电能','累计能耗') location, " +
                "DATE_FORMAT(l.io_time,'%Y-%m-%d') day,round(t.value - l.value ,1)value from (select a.resource_id, a.resource_name,a.io_time,a.`value`  " +
                " from tbl_io_energy a, " +
                "      (select resource_id, max(io_time) io_time " +
                "       from tbl_io_energy " +
                "       where io_time >= CURDATE() - interval 1 day and io_time < CURDATE() " +
                "         and sys_name = '能耗系统' " +
                "         and resource_name like ('门诊楼地下一层%') " +
                "        and resource_name like ('%进线柜%') " +
                "    and resource_name like ('%正向有功电能') " +
                "       GROUP BY resource_id) b " +
                " where a.resource_id = b.resource_id " +
                "   and a.io_time = b.io_time) l, " +
                "  (select a.resource_id, a.resource_name,a.io_time,a.`value`  " +
                " from tbl_io_energy a, " +
                "      (select resource_id, max(io_time) io_time " +
                "       from tbl_io_energy " +
                "       where io_time >= CURDATE()  " +
                "         and sys_name = '能耗系统' " +
                "         and resource_name like ('门诊楼地下一层%') " +
                "     and resource_name like ('%进线柜%') " +
                "     and resource_name like ('%正向有功电能') " +
                "       GROUP BY resource_id) b " +
                " where a.resource_id = b.resource_id " +
                "   and a.io_time = b.io_time) t  " +
                "  where l.resource_id = t.resource_id ";
        try {
            return yczyy.query(sql,_mapper_homeEnergy);
        }catch (Exception e){
            log.error("",e);
            return null;
        }
    }

    private final RowMapper<HomeSecurityResponse> _mapper_homeSecurity = new BeanPropertyRowMapper<>(HomeSecurityResponse.class);
    private final RowMapper<HomeParkResponse> _mapper_homePark= new BeanPropertyRowMapper<>(HomeParkResponse.class);
    private final RowMapper<HomeCtpResponse> _mapper_homeCtp = new BeanPropertyRowMapper<>(HomeCtpResponse.class);
    private final RowMapper<HomeLocationSecurityResponse> _mapper_homeLocationSecurity= new BeanPropertyRowMapper<>(HomeLocationSecurityResponse.class);
    private final RowMapper<HomeEnergy> _mapper_homeEnergy = new BeanPropertyRowMapper<>(HomeEnergy.class);
    @Autowired
    JdbcTemplate yczyy;
    @Autowired
    JdbcTemplate ctp;
    @Autowired
    JdbcTemplate xunGeng;
}
